﻿Option Explicit On
Option Strict On
Imports System.Data
Imports System.Text
Imports MySql.Data.MySqlClient
Imports System.Configuration
Public Class frmManagerEquipment
    Dim Conn As New MySqlConnection()
    Dim com As New MySqlCommand()
    Dim sb As New StringBuilder()
    Dim dt As DataTable
    Dim dr As MySqlDataReader
    Dim tr As MySqlTransaction
    Dim id As String

    Private Sub frmManagerEquipment_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim strConn As String = ConfigurationManager.ConnectionStrings("Equ").ConnectionString
        If Conn.State = ConnectionState.Open Then
            Conn.Close()
        End If
        Conn.ConnectionString = strConn
        Conn.Open()
        ShowData()
        FormatData()
    End Sub

    Private Sub ShowData()
        sb.Remove(0, sb.Length)
        sb.Append("SELECT * FROM item ")
        Dim sql As String = sb.ToString()
        With com
            .CommandText = sql
            .CommandType = CommandType.Text
            .Connection = Conn
            dr = .ExecuteReader()
            If dr.HasRows Then
                dt = New DataTable()
                dt.Load(dr)
                data.DataSource = dt
            Else
                data.DataSource = Nothing
            End If
        End With
        dr.Close()
    End Sub

    Private Sub FormatData()
        If data.RowCount > 0 Then
            With data
                .Columns(0).HeaderText = "รหัส"
                .Columns(1).HeaderText = "ชื่อ"
                .Columns(2).HeaderText = "ประเภท"
                .Columns(3).HeaderText = "จำนวน"
                .Columns(0).Width = 50
                .Columns(1).Width = 150
                .Columns(2).Width = 150
                .Columns(3).Width = 100
            End With
        End If
    End Sub

    Private Sub txtSearch_KeyDown(sender As Object, e As KeyEventArgs) Handles txtSearch.KeyDown
        If txtSearch.Text.Trim() = "" Then
            Exit Sub
        End If
        If e.KeyCode = Keys.Enter Then
            sb.Remove(0, sb.Length)
            sb.Append("SELECT * FROM item WHERE Name LIKE '%" & txtSearch.Text & "%'")
            Dim sql As String = sb.ToString()
            With com
                .CommandType = CommandType.Text
                .CommandText = sql
                .Connection = Conn
                dr = .ExecuteReader()
                If dr.HasRows() Then
                    dt = New DataTable()
                    dt.Load(dr)
                    data.DataSource = dt
                Else
                    data.DataSource = Nothing
                End If
                dr.Close()
            End With
        End If
    End Sub

    Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click
        sb.Remove(0, sb.Length)
        sb.Append("SELECT * FROM item WHERE Name LIKE '%" & txtSearch.Text & "%'")
        Dim sql As String = sb.ToString()
        With com
            .CommandType = CommandType.Text
            .CommandText = sql
            .Connection = Conn
            dr = .ExecuteReader()
            If dr.HasRows() Then
                dt = New DataTable()
                dt.Load(dr)
                data.DataSource = dt
            Else
                data.DataSource = Nothing
            End If
            dr.Close()
        End With
    End Sub

    Private Sub btnAdd_Click(sender As Object, e As EventArgs) Handles btnAdd.Click
        If txtName.Text.Trim() = "" Or txtAmount.Text.Trim() = "" Then
            MessageBox.Show("กรุณาป้อนข้อมูลให้ครบด้วย !!!", "คำเตือน", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End If
        If MessageBox.Show("คุณต้องการเพิ่มอุปกรณ์ใหม่ ใช่หรือไม่ ?", "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
            tr = Conn.BeginTransaction()
            Try
                sb.Remove(0, sb.Length)
                sb.Append("INSERT INTO item (Name,Type,Amount) ")
                sb.Append("VALUES (@name,@type,@amount)")
                Dim sql As String = sb.ToString()
                With com
                    .CommandText = sql
                    .CommandType = CommandType.Text
                    .Connection = Conn
                    .Transaction = tr
                    .Parameters.Clear()
                    .Parameters.Add("@name", MySqlDbType.VarChar).Value = txtName.Text
                    If cbbType.Text = "ยืม-คืน" Then
                        .Parameters.Add("@type", MySqlDbType.Enum).Value = "1"
                    Else
                        .Parameters.Add("@type", MySqlDbType.Enum).Value = "0"
                    End If
                    .Parameters.Add("@amount", MySqlDbType.Int32).Value = CInt(txtAmount.Text.Trim())
                    .ExecuteNonQuery()
                End With
                tr.Commit()
                MessageBox.Show("เพิ่มข้อมูลเรียบร้อยแล้ว !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information)
                ClearAllData()
                ShowData()
                FormatData()
            Catch ex As Exception
                MessageBox.Show("คุณกรอกข้อมูลไม่ถูกต้อง !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information)
                tr.Rollback()
            End Try
        End If
        txtName.Focus()
    End Sub

    Private Sub ClearAllData()
        txtAmount.Clear()
        txtName.Clear()
        txtSearch.Clear()
        cbbType.Text = "ยืม-คืน"
        btnEdit.Enabled = False
        btnDel.Enabled = False
        id = ""
    End Sub

    Private Sub data_CellMouseUp(sender As Object, e As DataGridViewCellMouseEventArgs) Handles data.CellMouseUp
        If e.RowIndex = -1 Then
            Exit Sub
        End If
        txtName.Text = data.Rows(e.RowIndex).Cells("Name").Value.ToString()
        txtAmount.Text = data.Rows(e.RowIndex).Cells("Amount").Value.ToString()
        If data.Rows(e.RowIndex).Cells("Type").Value.ToString() = "0" Then
            cbbType.Text = "เบิก"
        Else
            cbbType.Text = "ยืม-คืน"
        End If
        id = data.Rows(e.RowIndex).Cells("idItem").Value.ToString()
        btnEdit.Enabled = True
        btnDel.Enabled = True
    End Sub

    Private Sub btnEdit_Click(sender As Object, e As EventArgs) Handles btnEdit.Click
        If id = "" Then
            MessageBox.Show("กรุณาเลือกข้อมูลในตารางก่อน !!!", "คำเตือน", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End If
        If txtName.Text.Trim() = "" Or txtAmount.Text.Trim() = "" Then
            MessageBox.Show("กรุณากรอกข้อมูลให้ครบด้วย !!!", "คำเตือน", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End If
        If MessageBox.Show("คุณต้องการแก้ไขข้อมูล ใช่หรือไม่ ?", "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
            tr = Conn.BeginTransaction()
            Try
                sb.Remove(0, sb.Length)
                sb.Append("UPDATE item ")
                sb.Append("SET Name=@name,Type=@type,Amount=@amount ")
                sb.Append("WHERE idItem = @id")
                Dim sql As String = sb.ToString()

                With com
                    .CommandType = CommandType.Text
                    .CommandText = sql
                    .Connection = Conn
                    .Transaction = tr
                    .Parameters.Clear()
                    .Parameters.Add("@id", MySqlDbType.Int32).Value = CInt(id)
                    .Parameters.Add("@name", MySqlDbType.VarChar).Value = txtName.Text
                    If cbbType.Text = "ยืม-คืน" Then
                        .Parameters.Add("@type", MySqlDbType.Enum).Value = "1"
                    Else
                        .Parameters.Add("@type", MySqlDbType.Enum).Value = "0"
                    End If
                    .Parameters.Add("@amount", MySqlDbType.Int32).Value = CInt(txtAmount.Text.Trim())
                    Dim result As Integer = .ExecuteNonQuery()
                    If result = 0 Then
                        tr.Rollback()
                        MessageBox.Show("ข้อมูลไม่ถูกต้องกรุณาตรวจสอบใหม่ !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information)
                        txtName.SelectAll()
                    Else
                        tr.Commit()
                        MessageBox.Show("แก้ไขข้อมูลเรียบร้อยแล้ว !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information)
                        ClearAllData()
                        ShowData()
                        FormatData()
                    End If
                End With
            Catch ex As Exception
                tr.Rollback()
                MessageBox.Show("เกิดข้อผิดพลาด เนื่องจาก " + ex.Message, "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End Try
        End If
        txtName.Focus()
    End Sub

    Private Sub btnDel_Click(sender As Object, e As EventArgs) Handles btnDel.Click
        If id = "" Then
            MessageBox.Show("กรุณาเลือกข้อมูลในตารางก่อน !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End If
        If MessageBox.Show("คุณต้องการทำงานการลบข้อมูลอุปกรณ์ ใช่หรือไม่ ?", "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
            tr = Conn.BeginTransaction()
            Try
                sb.Remove(0, sb.Length)
                sb.Append("DELETE FROM item WHERE idItem = " & id)
                Dim sql As String = sb.ToString()

                With com
                    .CommandText = sql
                    .CommandType = CommandType.Text
                    .Connection = Conn
                    .Transaction = tr
                    .ExecuteNonQuery()
                End With
                tr.Commit()
                MessageBox.Show("ลบข้อมูลเรียบร้อยแล้ว !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information)
                ClearAllData()
                ShowData()
                FormatData()
            Catch ex As Exception
                tr.Rollback()
            End Try
        End If
        txtName.Focus()
    End Sub
End Class